﻿using DataChangeTools.WebApi.Common.Attribute;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DataChangeTools.WebApi.Common
{
    /// <summary>
    /// 获取操作数据sql
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public static class SqlHelper<T> where T : class, new()
    {
        /// <summary>
        /// 实体结构对象
        /// </summary>
        static TableAttribute TableAttribute { get; set; }


        /// <summary>
        /// sql拼接参数结构体
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "GetSql")]
        public static string GetSql(SqlStruct<T> sqlStruct)
        {
            TableAttribute = (TableAttribute)typeof(T).GetCustomAttributes(typeof(TableAttribute), true)[0];
            string sql = string.Empty;
            if (sqlStruct.Id != 0 && !sqlStruct.IsDelete)//根据id查询
            {
                sql = GetById(sqlStruct);
            }
            else if (sqlStruct.Entity != null && !sqlStruct.IsReturnId && !sqlStruct.IsUpdate)//新增
            {
                sql = AddNew(sqlStruct);
            }
            else if (sqlStruct.Entity != null && sqlStruct.IsReturnId)//新增并返回id
            {
                sql = AddNewReturnId(sqlStruct);
            }
            else if ((sqlStruct.Id != 0 && sqlStruct.IsDelete))//删除
            {
                sql = Delete(sqlStruct);
            }
            else if (sqlStruct.Ids != null && sqlStruct.Ids.Count() > 0)//批删
            {
                sql = DeleteMany(sqlStruct);
            }
            else if (sqlStruct.Param?.Count > 0 && sqlStruct.IsDelete)//多条件删除
            {
                sql = DeleteParam(sqlStruct);
            }
            else if (sqlStruct.Entity != null && sqlStruct.IsUpdate)//更新
            {
                sql = Update(sqlStruct);
            }
            else if (sqlStruct.Param?.Count > 0 && !sqlStruct.IsDelete)//多条件查询
            {
                sql = QueryMany(sqlStruct);
            }
            else//直接查询
            {
                sql = $"select * from {TableAttribute.Name}";
            }
            return sql + ";";
        }

        /// <summary>
        /// 多条件查询
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "QueryMany")]
        private static string QueryMany(SqlStruct<T> sqlStruct)
        {
            string sql = $"SELECT * FROM {TableAttribute.Name}  WHERE 1=1";
            if (sqlStruct.Param?.FirstOrDefault(p => p.Key == true).Value != null)//直接匹配
            {
                foreach (var item in sqlStruct.Param.FirstOrDefault(p => p.Key == true).Value)
                {
                    sql += $" AND {item.Key} = '{item.Value}'";
                }
            }
            if (sqlStruct.Param?.FirstOrDefault(p => p.Key == false).Value != null)//模糊匹配
            {
                sql += " AND (";
                foreach (var item in sqlStruct.Param?.FirstOrDefault(p => p.Key == false).Value)
                {
                    sql += $" {item.Key} LIKE '%{item.Value}%' OR";
                }
                sql = sql.Substring(0, sql.Length - 2) + ")";
            }
            return sql;
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "Update")]
        private static string Update(SqlStruct<T> sqlStruct)
        {
            string sql = $"update {TableAttribute.Name} set";
            EntityManage entityManage = GetEntity(sqlStruct.Entity);
            foreach (var item in entityManage.keyValuePairs)
            {
                if (item.Key == "Createtime" || item.Key == "Creator" || item.Key == "Id")
                {
                    continue;
                }
                if (entityManage.ints.Contains(item.Key))
                {
                    sql += $" {item.Key} = {item.Value} ,";
                }
                else
                {
                    sql += $" {item.Key} = '{item.Value}' ,";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += $" where id = {entityManage.keyValuePairs.Where(p => p.Key == "Id").FirstOrDefault().Value}";
            return sql;
        }

        /// <summary>
        /// 多条件删除
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "DeleteParam")]
        private static string DeleteParam(SqlStruct<T> sqlStruct)
        {
            string sql = $"Delete From {TableAttribute.Name}  where 1 = 1";
            if (sqlStruct.Param?.FirstOrDefault(p => p.Key == true).Value != null)//直接匹配
            {
                foreach (var item in sqlStruct.Param.FirstOrDefault(p => p.Key == true).Value)
                {
                    sql += $" AND {item.Key} = '{item.Value}'";
                }
            }
            if (sqlStruct.Param?.FirstOrDefault(p => p.Key == false).Value != null)//模糊匹配
            {
                sql += " AND (";
                foreach (var item in sqlStruct.Param?.FirstOrDefault(p => p.Key == false).Value)
                {
                    sql += $" {item.Key} LIKE '%{item.Value}%' OR";
                }
                sql = sql.Substring(0, sql.Length - 2) + ")";
            }
            return sql;
        }

        /// <summary>
        /// 批删
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "DeleteMany")]
        private static string DeleteMany(SqlStruct<T> sqlStruct)
        {
            string sql = $"Delete From {TableAttribute.Name}  where ";
            for (int i = 0; i < sqlStruct.Ids.Length; i++)
            {
                if (i == sqlStruct.Ids.Length - 1)
                {
                    sql += $"id ={sqlStruct.Ids[i]}";
                }
                else
                {
                    sql += $"id ={sqlStruct.Ids[i]} or ";
                }
            }
            return sql;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "Delete")]
        private static string Delete(SqlStruct<T> sqlStruct)
        {
            if (string.IsNullOrWhiteSpace(sqlStruct.DeleteId)) sqlStruct.DeleteId = "id";
            return $"Delete From {TableAttribute.Name}  where {sqlStruct.DeleteId} = {sqlStruct.Id}";
        }

        /// <summary>
        /// 新增并返回id
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        private static string AddNewReturnId(SqlStruct<T> sqlStruct)
        {
            string values = "";
            string names = "";
            DisposeSql(sqlStruct.Entity, ref values, ref names);
            return $"insert  {TableAttribute.Name} ({names.Substring(0, names.Length - 1)}) output inserted.Id values ({values.Substring(0, values.Length - 1)})";
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "AddNew")]
        private static string AddNew(SqlStruct<T> sqlStruct)
        {
            string values = "";
            string names = "";
            DisposeSql(sqlStruct.Entity, ref values, ref names);
            return $"insert  {TableAttribute.Name} ({names.Substring(0, names.Length - 1)}) values ({values.Substring(0, values.Length - 1)})";
        }

        /// <summary>
        /// 根据Id查询
        /// </summary>
        /// <param name="sqlStruct"></param>
        /// <returns></returns>
        [Log(ActionName = "GetById")]
        private static string GetById(SqlStruct<T> sqlStruct)
        {
            return $"select * from {TableAttribute.Name} where id = {sqlStruct.Id}";
        }

        /// <summary>
        /// 处理sql拼接
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="values"></param>
        /// <param name="names"></param>
        private static void DisposeSql(T entity, ref string values, ref string names)
        {
            EntityManage entityManage = GetEntity(entity);
            foreach (var item in entityManage.keyValuePairs)
            {
                if (item.Key == "Id") continue;
                if (entityManage.ints.Contains(item.Key))
                {
                    values += item.Value + ",";
                }
                else if (entityManage.Dates.Contains(item.Key))
                {
                    values += "'" + item.Value + "',";
                    //values += item.ToString("YYYY-MM-dd HH:mm:ss") + ",";
                }
                else
                {
                    values += "'" + item.Value + "',";
                }
                names += item.Key + ',';
            }
        }

        /// <summary>
        /// 处理entity对象
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static EntityManage GetEntity(T entity)
        {
            EntityManage entityManage = new EntityManage();
            entityManage.keyValuePairs = new Dictionary<string, string>();
            entityManage.ints = new List<string>();
            entityManage.Dates = new List<string>();
            PropertyInfo[] properties = entity.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            if (properties.Length > 0)
            {
                for (int i = 0; i < properties.Length; i++)
                {
                    if (properties[i].PropertyType.Name == "Int32")
                    {
                        entityManage.ints.Add(properties[i].Name);
                    }
                    //PropertyType = {Name = "DateTime" FullName = "System.DateTime"}
                    if (properties[i].PropertyType.Name == "DateTime")
                    {
                        entityManage.Dates.Add(properties[i].Name);
                    }
                    var val = entity.GetType().GetProperty(properties[i].Name).GetValue(entity, null);
                    if (val != null)
                    {
                        if (properties[i].PropertyType.Name == "DateTime")
                        {
                            object value = properties[i].GetValue(entity, null);
                            //""
                            DateTime date = (DateTime)value;
                            entityManage.keyValuePairs.Add(properties[i].Name, date.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else {
                            entityManage.keyValuePairs.Add(properties[i].Name, val.ToString());
                        }
                    }
                        
                }
            }
            return entityManage;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [Log(ActionName = "Add")]
        public static bool Add(T entity)
        {
            string values = "";
            string names = "";
            DisposeSql(entity, ref values, ref names);
            string sql = $"insert  {TableAttribute.Name} ({names.Substring(0, names.Length - 1)}) values ({values.Substring(0, values.Length - 1)})";
            var result = DapperHelper.Add<T>(sql);
            return Convert.ToBoolean(result);
        }
    }

    /// <summary>
    /// entity处理结构体
    /// </summary>
    public struct EntityManage
    {
        public Dictionary<string, string> keyValuePairs { get; set; }
        public List<string> ints { get; set; }
        public List<string> Dates { get; set; }
    }

}
